import logging
from datetime import date
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

take_out_head_router = APIRouter(prefix="/takeoutHead", tags=["耗材领出管理"], dependencies=[Depends(oauth2_scheme)])


class TakeOutSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    ftakedateStart: str = Field(None, description="开始时间")
    ftakedateEnd: str = Field(None, description="结束时间")
    fbilltype: str = Field(None, description="单据类型(1出/0入)")
    ftakeuser: str = Field(None, description="领用人员")
    fstockno: str = Field(None, description="仓库编号")
    fbillno: str = Field(None, description="单号")
    fbillstate: str = Field(None, description="单据状态(1:未领走/2:已领走/8:已作废)")


@take_out_head_router.get("/list", summary="查询耗材领出记录")
def list_take_out(search: TakeOutSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_tc_takeout_head where 1=1 "
    if search.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param['site_no'] = search.fsiteNo
    if search.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param['fbillno'] = search.fbillno
    if search.ftakeuser is not None:
        sql = sql + " and ftakeuser = :ftakeuser "
        param['ftakeuser'] = search.ftakeuser
    if search.fstockno is not None:
        sql = sql + " and fstockno = :fstockno "
        param['fstockno'] = search.fstockno
    if search.fbilltype is not None:
        sql = sql + " and fbilltype = :fbilltype "
        param['fbilltype'] = search.fbilltype
    if search.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param['fbillstate'] = search.fbillstate
    if (search.ftakedateStart is not None) and (search.ftakedateEnd is not None):
        sql = sql + " and to_char(ftakedate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param['ftakedateStart'] = search.ftakedateStart
        param['ftakedateEnd'] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,ftakeuser,ftakedate,fstockno,fitemcount,fpiececount,"
    sql = sql + " ftotalamt,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype,fsrcbillno"
    sql = sql + " from lims.m_tc_takeout_head where 1=1"
    if search.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param['site_no'] = search.fsiteNo
    if search.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param['fbillno'] = search.fbillno
    if search.ftakeuser is not None:
        sql = sql + " and ftakeuser = :ftakeuser "
        param['ftakeuser'] = search.ftakeuser
    if search.fstockno is not None:
        sql = sql + " and fstockno = :fstockno "
        param['fstockno'] = search.fstockno
    if search.fbilltype is not None:
        sql = sql + " and fbilltype = :fbilltype "
        param['fbilltype'] = search.fbilltype
    if search.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param['fbillstate'] = search.fbillstate
    if (search.ftakedateStart is not None) and (search.ftakedateEnd is not None):
        sql = sql + " and to_char(ftakedate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param['ftakedateStart'] = search.ftakedateStart
        param['ftakedateEnd'] = search.ftakedateEnd
    if search.order == "desc":
        sql = sql + " order by  ftakedate,fbillno desc "
    else:
        sql = sql + " order by  ftakedate,fbillno  "
    sql = sql + " limit :limit offset :offset "
    param['offset'] = search.offset
    param['limit'] = search.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fmaterialno,b.fbuyprice,b.fqty,b.fprocdate,b.ftotamt,b.fmemo"
    sql_3 = sql_3 + " from lims.m_tc_takeout_head a,lims.m_tc_takeout_detail b where a.fbillno=b.fbillno "
    if search.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param['site_no'] = search.fsiteNo
    if search.fbillno is not None:
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param['fbillno'] = search.fbillno
    if search.ftakeuser is not None:
        sql_3 = sql_3 + " and a.ftakeuser = :ftakeuser "
        param['ftakeuser'] = search.ftakeuser
    if search.fstockno is not None:
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param['fstockno'] = search.fstockno
    if search.fbilltype is not None:
        sql_3 = sql_3 + " and a.fbilltype = :fbilltype "
        param['fbilltype'] = search.fbilltype
    if search.fbillstate is not None:
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param['fbillstate'] = search.fbillstate
    if (search.ftakedateStart is not None) and (search.ftakedateEnd is not None):
        sql_3 = sql_3 + " and to_char(a.ftakedate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param['ftakedateStart'] = search.ftakedateStart
        param['ftakedateEnd'] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    # print(sql_3)
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['ftakeuser'] = data[key][2]
        d1['ftakedate'] = data[key][3]
        d1['fstockno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = data[key][9]
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = data[key][11]
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        d1['fbilltype'] = data[key][14]
        d1['fsrcbillno'] = data[key][15]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fmaterialno'] = data_detail[j1][2]
                d2['fbuyprice'] = data_detail[j1][3]
                d2['fqty'] = data_detail[j1][4]
                d2['fprocdate'] = data_detail[j1][5]
                d2['ftotamt'] = data_detail[j1][6]
                d2['fmemo'] = data_detail[j1][7]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": search.limit, "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class TakeOutDetailCreate(BaseModel):
    """耗材领出明细创建模型"""
    fline: int = Field(..., description="行号")
    fmaterialno: str = Field(..., description="物料编号")
    fmaterialname: str = Field(..., description="物料名称")
    fbuyprice: float = Field(0, description="采购单价，默认值为0")
    fqty: float = Field(..., description="领出数量")
    ftotamt: Optional[float] = Field(None, description="总金额，未提供时自动计算为单价×数量")
    fprocdate: date = Field(..., description="处理日期")


class TakeOutHeaderCreate(BaseModel):
    """耗材领出表头创建模型"""
    fbillno: str = Field(..., description="领出单号")
    fsiteNo: str = Field(..., description="站点编号")
    ftakeuser: str = Field(..., description="领出人")
    ftakedate: date = Field(..., description="领出日期")
    fstockno: str = Field(..., description="仓库编号")
    fexplanation: Optional[str] = Field("", description="领出说明，默认为空字符串")
    details: List[TakeOutDetailCreate] = Field(..., description="领出明细列表")


@take_out_head_router.post("/create", summary="新增耗材领出记录")
def create_take_out(take: TakeOutHeaderCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = take.fbillno

        # 返回内容
        d1 = {}

        detail_list = []
        with db.begin():
            jsondata = take.details
            for data in jsondata:
                if data.ftotamt:
                    data.ftotamt = data.fbuyprice * data.fqty
                sql = "insert into lims.m_tc_takeout_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt , :fprocdate ,'')"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fmaterialno'] = data.fmaterialno
                d2['fmaterialname'] = data.fmaterialname
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['fqty'] = float(data.fqty)
                d2['ftotamt'] = float(data.ftotamt)
                d2['fmemo'] = ""
                d2['fprocdate'] = data.fprocdate
                detail_list.append(d2)

            sql = "insert into lims.m_tc_takeout_head(fbillno,fsite_no,ftakeuser,ftakedate ,fstockno,fitemcount,fpiececount,ftotalamt ,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :ftakeuser , :ftakedate , :fstockno , :fitemcount , :fpiececount , :ftotalamt , :fexplanation ,now(), :ftakeuser ,1,1)"

            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": take.fsiteNo,
                "ftakeuser": take.ftakeuser,
                "ftakedate": take.ftakedate,
                "fstockno": take.fstockno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": take.fexplanation
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = take.fsiteNo
            d1['ftakeuser'] = take.ftakeuser
            d1['ftakedate'] = take.ftakedate
            d1['fstockno'] = take.fstockno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = ""
            d1['fupddate'] = take.ftakedate
            d1['foperator'] = take.ftakeuser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@take_out_head_router.post("/update", summary="更新耗材领出记录")
def update_take_out_head(take: TakeOutHeaderCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = take.fbillno
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from lims.m_tc_takeout_detail where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            jsondata = take.details
            for data in jsondata:
                if data.ftotamt:
                    data.ftotamt = data.fbuyprice * data.fqty
                sql = "insert into lims.m_tc_takeout_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt , :fprocdate ,'')"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt

                sql = "update lims.m_tc_takeout_head "
                sql = sql + " set fstockno= :fstockno ,fexplanation= :fexplanation "
                sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
                sql = sql + ",fupddate=now(),foperator= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fstockno": take.fstockno,
                    "fitemcount": fitemcount,
                    "fpiececount": fpiececount,
                    "ftotalamt": ftotalamt,
                    "fexplanation": take.fexplanation,
                    "userid": user_id
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@take_out_head_router.post("/delete/{fbillno}", summary="删除耗材领出记录")
def delete_take_out(fbillno: str = Path(description="记录编号"), db: Session = Depends(get_db),
                    token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            sql = "select count(1) from lims.m_tc_takeout_head where fbillno= :fbillno and fbillstate>1"
            count = db.execute(text(sql), {"fbillno": fbillno}).scalar()
            if count > 0:
                return BaseResponse.error(message="该订单不能删除")
            sql = "delete from lims.m_tc_takeout_detail where fbillno= ：fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            sql = "delete from lims.m_tc_takeout_head where fbillno='" + id + "'"
            db.execute(text(sql), {"fbillno": fbillno})
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
